/*******************************************************************************
 * Copyright (c) 2009-2011 ETCORE Labs. All rights reserved. 
 * 
 * This software is the confidential and proprietary information of ETCORE Labs.
 * You shall not disclose such confidential information and shall use it
 * only in accordance with the terms of the license agreement you entered into
 * with ETCORE Labs. 
 * 
 * Support can be obtained from support forums at:  http://www.etcore.com/
 * /
 *******************************************************************************/
package etcore.webapp.grouptalk.user.dao;

import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

import etcore.webapp.grouptalk.common.dao.BaseDAO;
import etcore.webapp.grouptalk.common.dao.HibernateSessionFactory;
import etcore.webapp.grouptalk.user.object.Focus;

/**
 * An instance of FocusDAO is a data access object used to access Focus, which
 * contains query and update operations
 * 
 * @author ETCORE GroupTalk Developers
 * 
 */
public class FocusDAO extends BaseDAO {
	/**
	 * Find Focus instances
	 * 
	 * @param begin
	 *            start position
	 * @param length
	 *            count of Focus instances
	 * @return Focus instances
	 */
	public List<Focus> findAll(int begin, int length) {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Focus.class.getSimpleName());
		sqlStringBuffer.append(" order by id desc");
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setFirstResult(begin);
		queryObject.setMaxResults(length);
		@SuppressWarnings("unchecked")
		List<Focus> results = queryObject.list();
		return results;

	}

	 
	public List<Focus> findFocuses(int begin, int length) {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Focus.class.getSimpleName());
		sqlStringBuffer.append(" where STRCMP(now(),addtime)>=0 order by id desc");
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setFirstResult(begin);
		queryObject.setMaxResults(length);
		@SuppressWarnings("unchecked")
		List<Focus> results = queryObject.list();
		return results;

	}

	/**
	 * Find the count of Focus instances
	 * 
	 * @return count of Focus instances
	 */
	public long findAllSize() {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("select count(*) from ");
		sqlStringBuffer.append(Focus.class.getSimpleName());
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		Object results = queryObject.uniqueResult();
		return (results == null) ? 0 : ((Number) results).longValue();

	}
	public long findAllFollowedSizeBetweenAdmins() {

		Session session = HibernateSessionFactory.getSession();
		String queryString = "SELECT count(f.id)  as ct  FROM gt_focus f,gt_member m,gt_group  g,gt_member m2 WHERE f.fromUser=m.userName AND m2.userName=f.toUser AND m2.gid=g.id AND m.gid=g.id AND g.name='ROOTS'";

		SQLQuery sqlQuery = session.createSQLQuery(queryString).addScalar("ct",
				Hibernate.INTEGER);

		@SuppressWarnings("rawtypes")
		List results = sqlQuery.list();

		return (results == null||results.size()==0)  ? 0 : ((Number) results.get(0)).longValue();

	}
	public long findAllFollowedSizeBetweenAdmins(String inputDate) {

		Session session = HibernateSessionFactory.getSession();
		String queryString = "SELECT count(f.id) as ct FROM gt_focus f,gt_member m,gt_group  g,gt_member m2 WHERE  (TO_DAYS('"+inputDate+"')-TO_DAYS(f.addTime))=0 and f.fromUser=m.userName AND m2.userName=f.toUser AND m2.gid=g.id AND m.gid=g.id AND g.name='ROOTS'";

		SQLQuery sqlQuery = session.createSQLQuery(queryString).addScalar("ct",
				Hibernate.INTEGER);

		@SuppressWarnings("rawtypes")
		List results = sqlQuery.list();

		return (results == null||results.size()==0)  ? 0 : ((Number) results.get(0)).longValue();

	}
	public long findSizeOfNotFollowedAdmin(String fromUser){
		Session session = HibernateSessionFactory.getSession();
		String queryString = "SELECT count(m.id) as ct  FROM gt_member m,gt_group  g WHERE    m.gid=g.id AND g.name='ROOTS' and m.userName<>? and m.userName not in (select f.toUser from gt_focus f where f.fromUser=?) ";

		SQLQuery sqlQuery = session.createSQLQuery(queryString).addScalar("ct",
				Hibernate.INTEGER);
		sqlQuery.setParameter(0, fromUser);
		sqlQuery.setParameter(1, fromUser);
		@SuppressWarnings("rawtypes")
		List results = sqlQuery.list();

		return (results == null||results.size()==0)  ? 0 : ((Number) results.get(0)).longValue();
	}

	public long findSizeOfFollowedAdmin(String fromUser, String date){
		Session session = HibernateSessionFactory.getSession();
		String queryString = "SELECT count(m.id) as ct  FROM gt_member m,gt_group g WHERE    m.gid=g.id AND g.name='ROOTS' and m.userName<>? and m.userName not in (select f.toUser from gt_focus f where f.fromUser=?) ";

		SQLQuery sqlQuery = session.createSQLQuery(queryString).addScalar("ct",
				Hibernate.INTEGER);
		sqlQuery.setParameter(0, fromUser);
		sqlQuery.setParameter(1, fromUser);
		@SuppressWarnings("rawtypes")
		List results = sqlQuery.list();

		return (results == null||results.size()==0)  ? 0 : ((Number) results.get(0)).longValue();
	}
	public List<String>   findNotFollowedAdmins(String fromUser,int begin, int length) {

		Session session = HibernateSessionFactory.getSession();
		String queryString = "SELECT m.userName FROM gt_member m,gt_group  g WHERE    m.gid=g.id AND g.name='ROOTS' and m.userName<>? and m.userName not in (select f.toUser from gt_focus f where f.fromUser=?) ";

		Query queryObject = session.createSQLQuery(queryString);
		queryObject.setParameter(0, fromUser);
		queryObject.setParameter(1, fromUser);
		queryObject.setFirstResult(begin);
		queryObject.setMaxResults(length);
		@SuppressWarnings("unchecked")
		List<String> results =  queryObject.list();

		return results;

	}
	/**
	 * Find Focus instances by fromUser and toUser
	 * 
	 * @param fromUser
	 *            the person who follows toUser
	 * @param toUser
	 *            the person who is followed by fromUser
	 * @return Focus instance
	 */
	public Focus findBy2Properties(String fromUser, String toUser) {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Focus.class.getSimpleName());
		sqlStringBuffer
				.append(" as model where model.fromUser = ? and model.toUser=?");

		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, fromUser);
		queryObject.setParameter(1, toUser);
		@SuppressWarnings("rawtypes")
		List results = queryObject.list();
		if (results != null && results.size() > 0)
			return (Focus) results.get(0);
		return null;

	}

	/**
	 * Find Focus instance by id
	 * 
	 * @param id
	 *            Focus id
	 * @return Focus instance
	 */
	public Focus findById(java.lang.Integer id) {
		Session session = HibernateSessionFactory.getSession();
		Focus instance = (Focus) session.get(Focus.class.getName(), id);
		return instance;

	}

	/**
	 * Find Focus instances by a property value
	 * 
	 * @param propertyName
	 *            property name
	 * @param value
	 *            property value
	 * @return Focus instances
	 */
	public List<Focus> findByProperty(String propertyName, Object value) {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Focus.class.getSimpleName());
		sqlStringBuffer.append(" as model where model.");
		sqlStringBuffer.append(propertyName);
		sqlStringBuffer.append("= ?");
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, value);
		@SuppressWarnings("unchecked")
		List<Focus> results = queryObject.list();
		return results;

	}

	/**
	 * Find Focus instances by a property value
	 * 
	 * @param propertyName
	 *            property name
	 * @param value
	 *            property value
	 * @param begin
	 *            start position
	 * @param length
	 *            count of instances
	 * @return Focus instances
	 */
	public List<Focus> findByProperty(String propertyName, Object value,
			int begin, int length) {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Focus.class.getSimpleName());
		sqlStringBuffer.append(" as model where model.");
		sqlStringBuffer.append(propertyName);
		sqlStringBuffer.append("= ?");
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, value);
		queryObject.setFirstResult(begin);
		queryObject.setMaxResults(length);
		@SuppressWarnings("unchecked")
		List<Focus> results = queryObject.list();

		return results;

	}

	/**
	 * Find the count of Focus instances by a property value
	 * 
	 * @param propertyName
	 *            property name
	 * @param value
	 *            property value
	 * @return count of Focus instances
	 */
	public long findSizeByProperty(String propertyName, Object value) {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("select count(*) from ");
		sqlStringBuffer.append(Focus.class.getSimpleName());
		sqlStringBuffer.append(" as model where model.");
		sqlStringBuffer.append(propertyName);
		sqlStringBuffer.append("= ?");
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, value);
		Object results = queryObject.uniqueResult();
		return (results == null) ? 0 : ((Number) results).longValue();

	}

	/**
	 * Find those with most followers.
	 * 
	 * @param begin
	 *            start position
	 * @param length
	 *            count of
	 * @return a list of object array,which contains toUser and the count of
	 *         followers.
	 */
	@SuppressWarnings("rawtypes")
	public List findUserStars(int begin, int length) {

		Session session = HibernateSessionFactory.getSession();
		String queryString = "";

		queryString = "select gt_focus.toUser, count(gt_focus.toUser),gt_user.avatar from gt_focus,gt_user where gt_user.userName=gt_focus.toUser group by gt_focus.toUser order by count(gt_focus.toUser) desc";

		Query queryObject = session.createSQLQuery(queryString);

		queryObject.setFirstResult(begin);
		queryObject.setMaxResults(length);
		List results = queryObject.list();

		return results;

	}
	

	 
	@SuppressWarnings("unchecked")
	public List<Object[]> findAdminsByMaxHisFollowed(int maxFollowed,int begin, int length) {

		Session session = HibernateSessionFactory.getSession();
		String queryString = "SELECT  m.userName,(SELECT COUNT(*) FROM gt_focus f2, gt_member m2 WHERE m.userName=f2.fromUser AND f2.toUser=m2.userName AND m2.gid=g.id) AS d   FROM gt_member m,gt_group g WHERE    m.gid=g.id AND g.name='ROOTS'  GROUP BY m.userName HAVING d<"+maxFollowed;

		 

		Query queryObject = session.createSQLQuery(queryString);

		queryObject.setFirstResult(begin);
		queryObject.setMaxResults(length);
		List<Object[]> results = queryObject.list();

		return results;

	}
	public long findAdminsSizeByMaxHisFollowed(long  maxFollowed){
		Session session = HibernateSessionFactory.getSession();
		String queryString = "select count(*) as ct from (SELECT  m.userName,(SELECT COUNT(*) FROM gt_focus f2, gt_member m2 WHERE m.userName=f2.fromUser AND f2.toUser=m2.userName AND m2.gid=g.id) AS d   FROM gt_member m,gt_group g WHERE    m.gid=g.id AND g.name='ROOTS'  GROUP BY m.userName HAVING d<"+maxFollowed+")sample";

		SQLQuery sqlQuery = session.createSQLQuery(queryString).addScalar("ct",
				Hibernate.INTEGER);
	 
		@SuppressWarnings("rawtypes")
		List results = sqlQuery.list();

		return (results == null||results.size()==0)  ? 0 : ((Number) results.get(0)).longValue();
	}
	
 
	/**
	 * Check whether a Focus instance with fromUser and toUser exists
	 * 
	 * @param fromUser
	 *            the person who follows toUser
	 * @param toUser
	 *            the person who is followed by fromUser
	 * @return true or false
	 */
	public boolean isAdded(String fromUser, String toUser) {
		Object result = findBy2Properties(fromUser, toUser);
		if (result != null)
			return true;
		return false;
	}

}